﻿using System;
using System.Media;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Runtime.InteropServices;
using Access = Microsoft.Office.Interop.Access;

namespace Macl
{
    [ComVisible(true)]
    [Guid("C598F825-20A4-43aa-8B92-A003A7324DBD")]
    [ClassInterface(ClassInterfaceType.None)]
    [ProgId("M3DatabaseUtilities")]
    public class M3DatabaseUtilities : IM3DatabaseUtilities
    {
        Access.Application oAccess = ((Access.Application)
                (Marshal.GetActiveObject("Access.Application")));
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="MyDestinationpathAndFile"></param>
        /// <returns></returns>
        public bool BackupCurrentDatabase(string MyDestinationpathAndFile)        
        {               
            try
            {
                File.Copy(oAccess.CurrentProject.FullName, MyDestinationpathAndFile);
                return true;                
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="MySourceDatabase"></param>
        /// <param name="MyDestinationDump"></param>
        /// <param name="GenerateLogFile"></param>
        /// <returns></returns>
        public bool CompactRepairExternal(string MySourceDatabase, 
            string MyDestinationDump, bool GenerateLogFile)
        {
            try
            {
                oAccess.Application.CompactRepair(MySourceDatabase, MyDestinationDump, GenerateLogFile);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }
                
        public int VBAReferencesCount
        {          
            get { return oAccess.References.Count; }            
        }

        public bool VBAAddReferenceFromFile(string MyFilePath)
        {   
            try
            {
                oAccess.References.AddFromFile(MyFilePath);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        public bool VBAAddReferenceFromGUID(string MyGUID, int Major, int Minor)
        {
            try
            {
                oAccess.References.AddFromGuid(MyGUID, Major, Minor);                 
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        public bool ConvertCurrentDatabase(string MyDestinationPathAndFile, int ToVersion)
        {
            try
            {
                if (ToVersion == 2)
                {
                    oAccess.ConvertAccessProject(oAccess.CurrentProject.FullName, 
                        MyDestinationPathAndFile, Access.AcFileFormat.acFileFormatAccess2);                     
                }
                else if (ToVersion == 95)
                {
                    oAccess.ConvertAccessProject(oAccess.CurrentProject.FullName, 
                        MyDestinationPathAndFile, Access.AcFileFormat.acFileFormatAccess95);                    
                }
                else if (ToVersion == 97)
                {
                    oAccess.ConvertAccessProject(oAccess.CurrentProject.FullName, 
                        MyDestinationPathAndFile, Access.AcFileFormat.acFileFormatAccess97);
                }
                else if (ToVersion == 2000)
                {
                    oAccess.ConvertAccessProject(oAccess.CurrentProject.FullName, 
                        MyDestinationPathAndFile, Access.AcFileFormat.acFileFormatAccess2000);
                }
                else if (ToVersion == 2002)
                {
                    oAccess.ConvertAccessProject(oAccess.CurrentProject.FullName, 
                        MyDestinationPathAndFile, Access.AcFileFormat.acFileFormatAccess2002);
                }
                else if (ToVersion == 2007)
                {
                    oAccess.ConvertAccessProject(oAccess.CurrentProject.FullName, 
                        MyDestinationPathAndFile, Access.AcFileFormat.acFileFormatAccess2007);
                }               
                return true;            
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        public string GenerateGUID()
        {
            try
            {
                Guid guid = Guid.NewGuid();
                return guid.ToString();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public string GetAccessVersion
        {         
            get { return oAccess.Version; }                        
        }

        /// <summary>
        /// You must create a Table with fields:
        /// 
        ///  - User
        ///  - ErrNumber
        ///  - ErrDescription
        ///  - Date
        ///  
        /// Only work for CurrentProject
        /// </summary>
        /// <param name="MyTable"></param>
        /// <param name="ErrNumber"></param>
        /// <param name="ErrDescription"></param>
        /// <returns></returns>
        public bool LogAccessError(string MyTable, string ErrNumber, string ErrDescription)
        {
            using (OleDbConnection cnn = new OleDbConnection(oAccess.CurrentProject.ToString()))
            {
                string strSQL;
                strSQL = "INSERT INTO " + MyTable + " (mUser, mError, mDescription, mDate) " +
                    "VALUES (@User, @Error, @Description, @Date)";
                    
                OleDbCommand cmd = new OleDbCommand();
                try
                {
                    cmd.Parameters.AddWithValue("@User", oAccess.CurrentUser());
                    cmd.Parameters.AddWithValue("@Error", ErrNumber);
                    cmd.Parameters.AddWithValue("@Description", ErrDescription);
                    cmd.Parameters.AddWithValue("@Date", System.DateTime.UtcNow.ToString());
                    
                    cmd.CommandText = strSQL;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = cnn;
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                    cnn.Close();
                    return true;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    cnn.Close();
                    cmd.Dispose();
                    Marshal.ReleaseComObject(oAccess);
                }
            }
        }

        public bool TablesAreEqual(string MyTableSource, string MyTableToCompare, string MyIndexField)
        {                          
            try
            {
                OleDbConnection cnn;
                using (DataSet ds = new DataSet())
                {
                    OleDbDataAdapter da;
                    string cs = oAccess.CurrentProject.Connection.ToString();
                    string strSQL = String.Format("SELECT * FROM {0} LEFT JOIN {1} ON {0}.{2} = {1}.{2}WHERE {1}.{2} Is Null"
                        , MyTableSource, MyTableToCompare, MyIndexField);
                    cnn = new OleDbConnection(cs);
                    da = new OleDbDataAdapter(strSQL, cnn);
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count <= 0)
                        return true;
                    else
                        return false;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        public string PlayWAVFile(string myPath)
        {
            try
            {
                using (SoundPlayer objPlayer = new SoundPlayer { SoundLocation = myPath })
                {   
                    objPlayer.Play();
                    return "Ok";
                }             
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="myReport"></param>
        /// <param name="PDFileName"></param>
        /// <returns></returns>
        public bool ConvertReportToPDF(string myReport, string PDFileName)
        {
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatPDF, PDFileName,
                    System.Type.Missing, System.Type.Missing, System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);   
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="myReport"></param>
        /// <param name="XPSFileName"></param>
        /// <returns></returns>
        public bool ConvertReportToXPS(string myReport, string XPSFileName)
        {
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatXPS, XPSFileName,
                    System.Type.Missing, System.Type.Missing, System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="myReport"></param>
        /// <param name="HTMLFileName"></param>
        /// <returns></returns>
        public bool ConvertReportToHTML(string myReport, string HTMLFileName)
        {
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatHTML, HTMLFileName,
                    System.Type.Missing, System.Type.Missing, System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="myReport"></param>
        /// <param name="XLSXFileName"></param>
        /// <returns></returns>
        public bool ConvertReportToXLSX(string myReport, string XLSXFileName)
        {
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatXLSX, XLSXFileName,
                    System.Type.Missing, System.Type.Missing, System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="myReport"></param>
        /// <param name="RTFFileName"></param>
        /// <returns></returns>
        public bool ConvertReportToRTF(string myReport, string RTFFileName)
        {
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatXLSX, RTFFileName,
                    System.Type.Missing, System.Type.Missing, System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="myReport"></param>
        /// <param name="ASPFileName"></param>
        /// <returns></returns>
        public bool ConvertReportToASP(string myReport, string ASPFileName)
        {
            try
            {
                oAccess.DoCmd.OutputTo(Access.AcOutputObjectType.acOutputReport,
                    myReport, Access.Constants.acFormatASP, ASPFileName,
                    System.Type.Missing, System.Type.Missing, System.Type.Missing);
                return true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Marshal.ReleaseComObject(oAccess);
            }
        }
    }
}
